#!/user/bin/env jython
# -*- coding: utf-8 -*-

import csv
import sys
import os
from com.ziclix.python.sql import zxJDBC

########################################################################

#os.system("export CLASSPATH=`pwd`/sqlitejdbc-v056.jar")

print sys.getdefaultencoding()

DATABASE    = "Rebides.db"
JDBC_URL    = "jdbc:sqlite:%s"  % DATABASE
JDBC_DRIVER = "org.sqlite.JDBC"

########################################################################

def getListEstablishment():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(estabelecimento) from info")
	
	listEstablishment = []
	
	for row in cursor:
		listEstablishment.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listEstablishment
	#sys.exit(0)

def getListYears():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(ano_lectivo) from info")
	
	listYears = []
	
	for row in cursor:
		listYears.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listYears

def getListCategories():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(categoria) from info")
	
	listCategories = []
	
	for row in cursor:
		listCategories.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listCategories
	
def getListDegrees():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute("select distinct(grau) from info")
	
	listDegrees = []
	
	for row in cursor:
		listDegrees.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listDegrees
	
def getFirstDefaultQuery(establishment, year):
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	queryToExecute = "select distinct(nome) from info where estabelecimento = '" + establishment + "' and ano_lectivo = '" + year + "'"
	cursor.execute(queryToExecute)
	
	listResult = []
	
	for row in cursor:
		listResult.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listResult
	
def getSecondDefaultQuery(establishment, year, categorie):
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	queryToExecute = ("select distinct(nome) from info where estabelecimento = '" 
	+ establishment + "' and ano_lectivo = '" + year + "' and categoria= '" + 
	categorie + "'")
	cursor.execute(queryToExecute)
	
	listResult = []
	
	for row in cursor:
		listResult.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listResult

def getThirdDefaultQuery(establishment, year, degree):
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	queryToExecute = ("select distinct(nome) from info where estabelecimento = '" 
	+ establishment + "' and ano_lectivo = '" + year + "' and grau= '" + 
	degree + "'")
	cursor.execute(queryToExecute)
	
	listResult = []
	
	for row in cursor:
		listResult.append(unicode(str(row[0]), 'utf-8'))
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	
	return listResult

########################################################################

def getConnection(jdbc_url, driverName):
    """
        Given the name of a JDBC driver class and the url to be used 
        to connect to a database, attempt to obtain a connection to 
        the database.
    """

    try:
        # no user/password combo needed here, hence the None, None
        dbConn = zxJDBC.connect(jdbc_url, None, None, driverName)
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(-1)

    return dbConn

########################################################################
########################################################################
